/**
 * 
 */
package org.vsg.stock.bulk.io;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.postgresql.PGConnection;
import org.postgresql.copy.CopyManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.vsg.common.biz.exception.ServiceException;

/**
 * @author Bill Vison
 *
 */
@Service(value="postgreSQLDataImport")
public class PostgreSQLDataImportImpl implements PostgreSQLDataImport {
	
	
	private static final Logger logger = LoggerFactory.getLogger(PostgreSQLDataImportImpl.class);	


	@Resource
	private DataSource ds;
	
	private PGConnection conn;
	
	/* (non-Javadoc)
	 * @see org.vsg.stock.client.dataio.MySQLDataImport#connect()
	 */
	@Override
	public void connect() {
		// TODO Auto-generated method stub
		try {
			conn = (PGConnection)ds.getConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


	}

	
	
	
	@Override
	public boolean importData(String tableName, File file, String[] fields)
			throws ServiceException, IOException {
		
		StringBuilder sql = new StringBuilder();
		sql.append("copy ").append(tableName).append(" ");
		sql.append("(");
		for (int i =0 ; i < fields.length ; i++) {
			if (i > 0) {
				sql.append(",");
			}
			sql.append(fields[i]);
		}
		sql.append(")");
		//sql.append("from ").append(file);
		sql.append(" from STDIN ");
		sql.append("with ");
		sql.append("csv");
		sql.append(";");
		
		logger.debug("[Run SQL] : " + sql);


        try {
        	
        	CopyManager  cpManager = conn.getCopyAPI();
        	cpManager.copyIn( sql.toString() , new FileInputStream(file)  );
        	
        	logger.debug("done");
        	
        	return true;

        } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


		
		
		return false;
	}




	@Override
	public boolean importData(String tableName, InputStream is, String[] fields)
			throws ServiceException, IOException {
		// TODO Auto-generated method stub
		StringBuilder sql = new StringBuilder();
		sql.append("copy ").append(tableName).append(" ");
		sql.append("(");
		for (int i =0 ; i < fields.length ; i++) {
			if (i > 0) {
				sql.append(",");
			}
			sql.append(fields[i]);
		}
		sql.append(")");
		//sql.append("from ").append(file);
		sql.append(" from STDIN ");
		sql.append("with ");
		sql.append("csv");
		sql.append(";");
		
		logger.debug("[Run SQL] : " + sql);


        try {
        	
        	CopyManager  cpManager = conn.getCopyAPI();
        	cpManager.copyIn( sql.toString() , is  );
        	
        	logger.debug("done");
        	
        	return true;

        } catch (SQLException e) {
			// TODO Auto-generated catch block
        	e.printStackTrace();
        	throw new ServiceException( e.getCause() );

		}

	}




	/* (non-Javadoc)
	 * @see org.vsg.stock.client.dataio.MySQLDataImport#disconnect()
	 */
	@Override
	public void disconnect() {


		Connection conn = (Connection)this.conn;
		
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn = null;
		}
		
	}

	
	
	
	@Override
	public boolean importData(String tableName, InputStream is,
			String[] fields, Map<String, String> opts) throws ServiceException,
			IOException {
		StringBuilder sql = new StringBuilder();
		sql.append("copy ").append(tableName).append(" ");
		sql.append("(");
		for (int i =0 ; i < fields.length ; i++) {
			if (i > 0) {
				sql.append(",");
			}
			sql.append(fields[i]);
		}
		sql.append(")");
		sql.append(" from STDIN ");
		sql.append("with ");
		if ( opts != null) {
			String value = opts.get("delimiter");
			if ( value != null) {
				sql.append("DELIMITER AS ").append("'"+value+"'").append(" ");
			}
		}
		sql.append("csv");
		sql.append(";");
		
		logger.debug("[Run SQL] : " + sql);


        try {
        	
        	CopyManager  cpManager = conn.getCopyAPI();
        	cpManager.copyIn( sql.toString() , is  );
        	
        	logger.debug("done");
        	
        	return true;

        } catch (SQLException e) {
			// TODO Auto-generated catch block
        	e.printStackTrace();
        	throw new ServiceException( e.getCause() );

		}

	}




	/* (non-Javadoc)
	 * @see org.vsg.stock.client.dataio.MySQLDataImport#importData(java.lang.String, java.io.File)
	 */
	@Override
	public boolean importData(String tableName, File file) throws ServiceException , IOException{


		
		return false;
	}

}
